<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <title>User Authentication</title>
    <link rel="stylesheet" href="gettingStarted.css" type="text/css" />
    <meta name="generator" content="DocBook XSL Stylesheets V1.73.2" />
    <link rel="start" href="index.html" title="Getting Started with the Oracle Berkeley DB SQL APIs" />
    <link rel="up" href="dbsqlbasics.html" title="Chapter 1. Berkeley DB SQL: The Absolute Basics" />
    <link rel="prev" href="journaldirectory.html" title="The Journal Directory" />
    <link rel="next" href="unsupportedpragmas.html" title="Unsupported PRAGMAs" />
  </head>
  <body>
    <div xmlns="" class="navheader">
      <div class="libver">
        <p>Library Version 12.1.6.2</p>
      </div>
      <table width="100%" summary="Navigation header">
        <tr>
          <th colspan="3" align="center">User Authentication</th>
        </tr>
        <tr>
          <td width="20%" align="left"><a accesskey="p" href="journaldirectory.html">Prev</a> </td>
          <th width="60%" align="center">Chapter 1. Berkeley DB SQL: The Absolute Basics</th>
          <td width="20%" align="right"> <a accesskey="n" href="unsupportedpragmas.html">Next</a></td>
        </tr>
      </table>
      <hr />
    </div>
    <div class="sect1" lang="en" xml:lang="en">
      <div class="titlepage">
        <div>
          <div>
            <h2 class="title" style="clear: both"><a id="user_authentication"></a>User Authentication</h2>
          </div>
        </div>
      </div>
      <div class="toc">
        <dl>
          <dt>
            <span class="sect2">
              <a href="user_authentication.html#bdb_user_authentication">BDB User Authentication
                </a>
            </span>
          </dt>
          <dt>
            <span class="sect2">
              <a href="user_authentication.html#bdb_keystore_user_auth">BDB SQL Key-store Based User Authentication</a>
            </span>
          </dt>
        </dl>
      </div>
      <p>
                See the following section for more information:
            </p>
      <div class="itemizedlist">
        <ul type="disc">
          <li>
            <p>
                   <a class="xref" href="user_authentication.html#bdb_user_authentication" title="BDB User Authentication">BDB User Authentication
                </a>
                </p>
          </li>
          <li>
            <p>
                   <a class="xref" href="user_authentication.html#bdb_keystore_user_auth" title="BDB SQL Key-store Based User Authentication">BDB SQL Key-store Based User Authentication</a>
                </p>
          </li>
        </ul>
      </div>
      <div class="sect2" lang="en" xml:lang="en">
        <div class="titlepage">
          <div>
            <div>
              <h3 class="title"><a id="bdb_user_authentication"></a>BDB User Authentication
                </h3>
            </div>
          </div>
        </div>
        <p>
                    With the BDB user authentication extension, a database
                    can be marked as requiring authentication. To visit an
                    authentication-required BDB database, an authenticated
                    user must be logged into the database connection first. 
                    Once a BDB database is marked as authentication-required,
                    it cannot be converted back into a no-authentication-required
                    database. Encryption is mandatory if user authentication
                    is activated.
                 </p>
        <p>
                    By default a database does not require authentication.
                    The BDB user authentication module will be activated 
                    by adding the -DBDBSQL_USER_AUTHENTICATION
                    compile-time option. The client application must add
                    <code class="literal">lang/sql/generated/sqlite3.h</code> to work
                    with BDB user authentication. BDB user authentication
                    is based on <a class="ulink" href="http://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt" target="_top">
                    SQLite User Authentication</a>.
                </p>
        <p>
                    See the following sections for more information:
                </p>
        <div class="itemizedlist">
          <ul type="disc">
            <li>
              <p>
                   <a class="xref" href="user_authentication.html#the_interface" title="The Interface">The Interface</a>
                </p>
            </li>
            <li>
              <p>
                   <a class="xref" href="user_authentication.html#bootstrap" title="Bootstrap">Bootstrap</a>
                </p>
            </li>
            <li>
              <p>
                   <a class="xref" href="user_authentication.html#transaction" title="Transaction">Transaction</a>
                </p>
            </li>
            <li>
              <p>
                   <a class="xref" href="user_authentication.html#security_considerations" title="Security Considerations">Security Considerations</a>
                </p>
            </li>
          </ul>
        </div>
        <div class="sect3" lang="en" xml:lang="en">
          <div class="titlepage">
            <div>
              <div>
                <h4 class="title"><a id="the_interface"></a>The Interface</h4>
              </div>
            </div>
          </div>
          <p>
                        The users can use the following 3 ways to work with BDB
                        user authentication: 
                     </p>
          <div class="itemizedlist">
            <ul type="disc">
              <li>
                <p>via C APIs</p>
                <pre class="programlisting">   int sqlite3_user_authenticate(
     sqlite3 *db,           /* The database connection */
     const char *zUsername, /* Username */
     const char *aPW,       /* Password or credentials */
     int nPW                /* Number of bytes in aPW[] */
   );</pre>
                <pre class="programlisting">   int sqlite3_user_add(
     sqlite3 *db,           /* Database connection */
     const char *zUsername, /* Username to be added */
     const char *aPW,       /* Password or credentials */
     int nPW,               /* Number of bytes in aPW[] */
     int isAdmin            /* True to give new user admin privilege */
   );</pre>
                <pre class="programlisting">   int sqlite3_user_change(
     sqlite3 *db,           /* Database connection */
     const char *zUsername, /* Username to change */
     const void *aPW,       /* Modified password or credentials */
     int nPW,               /* Number of bytes in aPW[] */
     int isAdmin            /* Modified admin privilege for the user */
   );</pre>
                <pre class="programlisting">   int sqlite3_user_delete(
     sqlite3 *db,           /* Database connection */
     const char *zUsername  /* Username to remove */
   );</pre>
              </li>
              <li>
                <p>
                         Via the BDB SQL user authentication PRAGMAs below:
                         </p>
                <div class="itemizedlist">
                  <ul type="circle">
                    <li>
                      <p>
                                  <code class="literal">
                                  PRAGMA bdbsql_user_login="{USER_NAME}:{USER_PWD}";
                                  </code>
                                </p>
                    </li>
                    <li>
                      <p>
                                  <code class="literal">
                                  PRAGMA bdbsql_user_add="{USER_NAME}:{USER_PWD}:{IS_ADMIN}";
                                  </code>
                                </p>
                    </li>
                    <li>
                      <p>
                                  <code class="literal">
                                  PRAGMA bdbsql_user_edit="{USER_NAME}:{USER_PWD}:{IS_ADMIN}";
                                  </code>
                                </p>
                    </li>
                    <li>
                      <p>
                                  <code class="literal">
                                  PRAGMA bdbsql_user_delete="{USER_NAME}";
                                  </code>
                                </p>
                    </li>
                  </ul>
                </div>
              </li>
              <li>
                <p>
                           Via the BDB SQL shell commands as below:
                         </p>
                <div class="itemizedlist">
                  <ul type="circle">
                    <li>
                      <p>
                                  <code class="literal">
                                  .user login {USER_NAME} {USER_PWD}
                                  </code>
                                </p>
                    </li>
                    <li>
                      <p>
                                  <code class="literal">
                                  .user add {USER_NAME} {USER_PWD} {IS_ADMIN}
                                  </code>
                                </p>
                    </li>
                    <li>
                      <p>
                                  <code class="literal">
                                  .user edit {USER_NAME} {USER_PWD} {IS_ADMIN}
                                  </code>
                                </p>
                    </li>
                    <li>
                      <p>
                                  <code class="literal">
                                  .user delete {USER_NAME}
                                  </code>
                                </p>
                    </li>
                  </ul>
                </div>
              </li>
            </ul>
          </div>
          <p>
                    You can use the sqlite3_user_authenticate() interface to log in a
                    user into the database connection. Calling sqlite3_user_authenticate()
                    on a no-authentication-required database connection will return an
                    error. This is different from the original SQLite behavior.  
                </p>
          <p>
                    You can use the sqlite3_user_add()/sqlite3_user_delete() interfaces
                    to add/delete a user. It resuts in an error to call
                    sqlite3_user_add()/sqlite3_user_delete() on an authentication-required
                    database connection without an administrative user loged in. The currently
                    logged-in user cannot be deleted.  
                </p>
          <p>
                    You can use the sqlite3_user_change() interface to change a users login
                    credentials or admin privilege. Any user can change their own password, but
                    no user can change their own administrative privilege setting. Only an
                    administrative user can change another users login credentials or
                    administrative privilege setting. 
                </p>
          <p>
                    The sqlite3_set_authorizer() callback is modified to take a 7th parameter
                    which is the username of the currently logged in user, or NULL for a
                    no-authentication-required database.
                </p>
          <p>
                    When ATTACH-ing new database files to a connection, each newly attached
                    database that is an authentication-required database is checked using
                    the same username and password as provided to the main database. If that
                    check fails, then the ATTACH command fails with an SQLITE_AUTH error.
                </p>
        </div>
        <div class="sect3" lang="en" xml:lang="en">
          <div class="titlepage">
            <div>
              <div>
                <h4 class="title"><a id="bootstrap"></a>Bootstrap</h4>
              </div>
            </div>
          </div>
          <p>
                            No-authentication-required database becomes an
                            authentication-required database when the first user was
                            added into the BDB database. This is called user authentication
                            bootstrap. In bootstrap, the isAdmin parameter of the
                            sqlite3_user_add() call must be true.  After bootstrap, the
                            first added user is logged into the database connection.
                        </p>
        </div>
        <div class="sect3" lang="en" xml:lang="en">
          <div class="titlepage">
            <div>
              <div>
                <h4 class="title"><a id="transaction"></a>Transaction</h4>
              </div>
            </div>
          </div>
          <p>
                            BDB user authentication APIs
                            sqlite3_user_add()/sqlite3_user_change()/sqlite3_user_delete()
                            work in their own transaction. It results in an error to call
                            these APIs inside a transaction.
                        </p>
        </div>
        <div class="sect3" lang="en" xml:lang="en">
          <div class="titlepage">
            <div>
              <div>
                <h4 class="title"><a id="security_considerations"></a>Security Considerations</h4>
              </div>
            </div>
          </div>
          <p>
                            A BDB database is not considered as secure if it has
                            only BDB user authentication applied status. The
                            security issues are as follows: 
                        </p>
          <div class="itemizedlist">
            <ul type="disc">
              <li>
                <p>
                                 Anyone with access to the device can just open the
                                 database file in binary editor to see and modify the data.
                              </p>
              </li>
              <li>
                <p>
                                An authentication-required BDB database requires no
                                authentication if opened by a version of BDB that omits
                                the user authentication compile-time option
                              </p>
              </li>
            </ul>
          </div>
          <p>
                           Due to the above issues BDB encryption has to be turned
                           on when BDB user authentication is used. This requires the
                           user to provide an encryption key before calling any of
                           the authentication functions. If the database
                           is encrypted, sqlite3_key_v2() must be called first,
                           with the correct decryption key, prior to invoking
                           sqlite3_user_authenticate()/sqlite3_user_add().
                        </p>
          <p>
                           To open an existing, encrypted, authentication-required
                           database, the call sequence is:
                        </p>
          <pre class="programlisting">sqlite3_open_v2();
sqlite3_key_v2();
sqlite3_user_authenticate();
/* Database is now usable */</pre>
          <p>
                           To create a new, encrypted, authentication-required database,
                           the call sequence is:
                        </p>
          <pre class="programlisting">sqlite3_open_v2();
sqlite3_key_v2();
sqlite3_user_add();</pre>
        </div>
      </div>
      <div class="sect2" lang="en" xml:lang="en">
        <div class="titlepage">
          <div>
            <div>
              <h3 class="title"><a id="bdb_keystore_user_auth"></a>BDB SQL Key-store Based User Authentication</h3>
            </div>
          </div>
        </div>
        <p>
                   BDB SQL provides the key-store based user authentication to
                   allow the user to work easily with encryption and user
                   authentication together. In the key-store based user
                   authentication, encryption becomes mandatory if user
                   authentication is enabled, and user could just work with
                   user authentication API only and without the knowledge of
                   the encryption key.
                </p>
        <p>
                    You do this by storing the encryption key into a key-store
                    file. The encryption key stored in the key-store file is
                    encrypted. The key-store file, name ending with ".ks", 
                    is put under the same directory as the database
                    environment. Each authenticated user has one entry
                    in this key-store file. The entry contains the
                    user's name and the encryption key. When 
                    sqlite3_user_authenticate() is called, if the encryption
                    key is not applied to the database connection yet, BDB SQL
                    will find the user's entry in the key-store file, compute
                    the database encryption key with the user's password,
                    then apply the encryption key to the database connection.
                </p>
        <p>
                    You can enable the key-store based user authentication
                    by adding <code class="literal">-DBDBSQL_USER_AUTHENTICATION_KEYSTORE</code>
                    compile option.
                </p>
        <p>
                    See the following sections for more information:
                </p>
        <div class="itemizedlist">
          <ul type="disc">
            <li>
              <p>
                   <a class="xref" href="user_authentication.html#bdb_keystore_interface" title="Interface">Interface</a>
                </p>
            </li>
            <li>
              <p>
                   <a class="xref" href="user_authentication.html#keystore_bootstrap" title="Bootstrap">Bootstrap</a>
                </p>
            </li>
            <li>
              <p>
                   <a class="xref" href="user_authentication.html#user_log_in" title="User Log In">User Log In</a>
                </p>
            </li>
            <li>
              <p>
                   <a class="xref" href="user_authentication.html#keystore_transaction" title="Transaction">Transaction</a>
                </p>
            </li>
            <li>
              <p>
                   <a class="xref" href="user_authentication.html#the_lock_file" title="The Lock File">The Lock File</a>
                </p>
            </li>
          </ul>
        </div>
        <div class="sect3" lang="en" xml:lang="en">
          <div class="titlepage">
            <div>
              <div>
                <h4 class="title"><a id="bdb_keystore_interface"></a>Interface</h4>
              </div>
            </div>
          </div>
          <p> 
                         The key-store user authentication APIs work the same
                         with the non-keystore user authentication.
                       </p>
        </div>
        <div class="sect3" lang="en" xml:lang="en">
          <div class="titlepage">
            <div>
              <div>
                <h4 class="title"><a id="keystore_bootstrap"></a>Bootstrap</h4>
              </div>
            </div>
          </div>
          <p> 
                         No-authentication-required database becomes an
                         authentication-required database until the first user is
                         added into the BDB database. This is called user authentication
                         bootstrap. In bootstrap, the isAdmin parameter of the
                         sqlite3_user_add() call must be true. After bootstrap, the
                         first added user is logged into the database connection. There
                         are several cases related the the encryption key when doing
                         key-store based user authentication bootstrap:
                       </p>
          <div class="itemizedlist">
            <ul type="disc">
              <li>
                <p>
                             If the database file does not exist yet and user does
                             not provide his encryption key, a random generated key
                             will be applied to the database and be stored into the
                             key-store file. The call sequence is:
                            </p>
                <pre class="programlisting">sqlite3_open_v2();
sqlite3_user_add();</pre>
                <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
                  <h3 class="title">Note</h3>
                  <p>
                              It is recommended for the user to backup the key-store file,
                              especially when a generated random key is used.
                             </p>
                </div>
              </li>
              <li>
                <p>
                           If the database file does not exist yet and user provides
                           the encryption key, the encryption key provided will be
                           applied to the database and be stored into the key-store file.
                           The call sequence is:
                           </p>
                <pre class="programlisting">sqlite3_open_v2();
sqlite3_key_v2();
sqlite3_user_add();</pre>
              </li>
              <li>
                <p>
                           If the database file already exists, the database is
                           encrypted and the user provided the correct encryption
                           key, the encryption key provided will be stored into the
                           key-store file. The call sequence is:
                           </p>
                <pre class="programlisting">sqlite3_open_v2();
sqlite3_key_v2();
sqlite3_user_add();</pre>
              </li>
            </ul>
          </div>
          <p>The bootstrap fails in case:</p>
          <div class="itemizedlist">
            <ul type="disc">
              <li>
                <p>
                                 The database file already exists, the database is
                                 encrypted and the user provided an incorrect encryption
                                 key.
                                </p>
              </li>
              <li>
                <p>
                                The database file already exists but the database
                                is not encrypted.
                                </p>
              </li>
            </ul>
          </div>
        </div>
        <div class="sect3" lang="en" xml:lang="en">
          <div class="titlepage">
            <div>
              <div>
                <h4 class="title"><a id="user_log_in"></a>User Log In</h4>
              </div>
            </div>
          </div>
          <p>
                         As encryption key is already in the key-store file,
                         the user only needs to provide the user/password details
                         to work with the encrypted database. BDB SQL will compute
                         the encryption key from the key-store file and apply it
                         to the database connection. The call sequence is
                         as below:
                       </p>
          <pre class="programlisting">sqlite3_open_v2();
sqlite3_user_authenticate();
/* Database is now usable */</pre>
          <p>
                         The user can also provide the encryption key before
                         the sqlite3_user_authenticate() call. In this case,
                         BDB SQL will not visit the key-store file for the
                         encryption key. The call sequence is as below:
                       </p>
          <pre class="programlisting">sqlite3_open_v2();
sqlite3_key_v2();
sqlite3_user_authenticate();
/* Database is now usable */</pre>
        </div>
        <div class="sect3" lang="en" xml:lang="en">
          <div class="titlepage">
            <div>
              <div>
                <h4 class="title"><a id="keystore_transaction"></a>Transaction</h4>
              </div>
            </div>
          </div>
          <p>
                         BDB user authentication API
                         sqlite3_user_add()/sqlite3_user_change()/sqlite3_user_delete()
                         works in their own transaction. It will result in an error 
                         if you call these APIs inside a transaction.
                       </p>
        </div>
        <div class="sect3" lang="en" xml:lang="en">
          <div class="titlepage">
            <div>
              <div>
                <h4 class="title"><a id="the_lock_file"></a>The Lock File</h4>
              </div>
            </div>
          </div>
          <p>
                        BDB key-store user authentication uses a locking file to
                        ensure it behaves correctly in a multi-thread enviroment.
                        In rare cases, if a system or application crash occurs
                        while updating updating the key-store file, the
                        locking file may not be cleaned and the next
                        sqlite3_user_authenticate() call will be rejected. In
                        this case, user needs to clean the .lck file under the database
                        environment.
                      </p>
        </div>
      </div>
    </div>
    <div class="navfooter">
      <hr />
      <table width="100%" summary="Navigation footer">
        <tr>
          <td width="40%" align="left"><a accesskey="p" href="journaldirectory.html">Prev</a> </td>
          <td width="20%" align="center">
            <a accesskey="u" href="dbsqlbasics.html">Up</a>
          </td>
          <td width="40%" align="right"> <a accesskey="n" href="unsupportedpragmas.html">Next</a></td>
        </tr>
        <tr>
          <td width="40%" align="left" valign="top">The Journal Directory </td>
          <td width="20%" align="center">
            <a accesskey="h" href="index.html">Home</a>
          </td>
          <td width="40%" align="right" valign="top"> Unsupported PRAGMAs</td>
        </tr>
      </table>
    </div>
  </body>
</html>
